
package com.compuvest.feed

import groovy.sql.Sql
import java.text.SimpleDateFormat
import javax.naming.InitialContext

class MySQL {

    def sql

    def static final COMPUVESTCOM = 0
    def static final COMPUVESTUS  = 1
    def static final AMEX         = 2

    def static final dateFormatMysql  = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", java.util.Locale.US)
    def static final dateFormatGoogle = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss", java.util.Locale.US) //2011-08-28T22:03:38.000-07:00


    def MySQL() {

        def ds = new InitialContext().lookup("jdbc/mysql")
        sql = new Sql(ds)

        sql.eachRow('select VERSION()') {
            println ">>>>GoogleFeed::MySQL() version=${it[0]}"
        }
        sql.close()
    }

    def resetGoogleActive() {

        sql.executeUpdate("UPDATE LOW_PRIORITY sklad.itemsOnGoogle SET active=0,expdate='0000-00-00 00:00:00'")
        sql.close()
    }

    def setGoogleActive(entries) {

        def expdate
        sql.withBatch(entries.size(), "UPDATE LOW_PRIORITY sklad.itemsOnGoogle SET active=1,expdate=? WHERE accwareCatID=?") { row ->
            entries.each { accwareID, entity ->
                expdate = dateFormatGoogle.parse(entity.field['expdate'].toString())
                row.addBatch(dateFormatMysql.format(expdate), accwareID)
            }
        }
        sql.close()
    }

// <editor-fold defaultstate="collapsed" desc="getWeeklyItems()">
/*
SELECT it.item_id itemID,it.accwareCatID accwareID,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,CONCAT(IFNULL(gc.name,'Generic'),' > ',IFNULL(ct.name,'Generic')) catName,
it.stock_quantity qty,iv.alias vendorName,it.drone drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
IFNULL(COALESCE(
IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 0 AND 30 = 0,NULL,ct.apple0),
IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 31 AND 60 = 0,NULL,ct.apple1),
IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 61 AND 90 = 0,NULL,ct.apple2),
IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 91 AND 180 = 0,NULL,ct.apple3),
IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 181 AND 240 = 0,NULL,ct.apple4),
IF(DATEDIFF(NOW(),it.stockDate) >= 241 = 0,NULL,ct.apple5)
),0) + ct.discount discount,IF(it.active='Y',1,0) itemActive,ig.active googleActive
FROM sklad.items it
LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
LEFT JOIN sklad.itemsAdvert ad USING(item_id)
LEFT JOIN sklad.category ct USING(category_id)
LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
LEFT JOIN sklad.itemVendor iv USING(vendor_id)
LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
WHERE it.vendor_id=2 AND it.sell_price > 5
HAVING discount > 0 ORDER BY it.accwareCatID
LIMIT 0,1\G
AND it.accwareCatID='331004753-32'
AND it.accwareCatID IN ('320000086-02','320000081-02','351005935-82','556002387-00','311001258-02')
*/

    //def getWeeklyItems(page, subcatid, vends) {
    //        WHERE it.vendor_id=2 AND it.sell_price > 5 AND ad.froogle=TRUE AND it.category_id=${subcatid} AND it.vendor_id IN (${vends})
    def getWeeklyItems(page, pageSize) {

        def items = [:]
        def sellPrice, reslPrice, discount
        //def out = new StringBuilder()

        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IFNULL(COALESCE(
            IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 0 AND 30 = 0,NULL,ct.apple0),
            IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 31 AND 60 = 0,NULL,ct.apple1),
            IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 61 AND 90 = 0,NULL,ct.apple2),
            IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 91 AND 180 = 0,NULL,ct.apple3),
            IF(DATEDIFF(NOW(),it.stockDate) BETWEEN 181 AND 240 = 0,NULL,ct.apple4),
            IF((DATEDIFF(NOW(),it.stockDate) >= 241) = 0,NULL,ct.apple5)
            ),0) + ct.discount discount,IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.vendor_id=2 AND it.sell_price > 5 AND ig.send=1
            HAVING discount > 0
            ORDER BY it.accwareCatID """, page * pageSize, pageSize)
            //"ORDER BY it.accwareCatID ", page * 1, 1)
        { row ->

            sellPrice = row.sellPrice
            reslPrice = row.reslPrice
            discount = row.discount

            //out << "accwareCode=${row.accwareCode} discount=${row.discount}  sellPrice=${sellPrice}\n"
            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   sellPrice,
                    oldreslprice:   reslPrice,
                    sellprice:      (sellPrice - (sellPrice * discount / 100d)).round(2),
                    reslprice:      (reslPrice - (reslPrice * discount / 100d)).round(2),
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           row.itemActive ? (row.googleActive ? 'update' : 'insert') : 'delete'    // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()
        //println out

        return items
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getExparedStock()">

    def getExparedStock(page) {

        def items = [:]
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.sell_price > 5 AND ig.send=1 AND (ig.updated BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW())
            ORDER BY it.accwareCatID """, page * 20, 20)
            //"ORDER BY it.accwareCatID ", page * 2, 2)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   row.sellPrice,
                    oldreslprice:   row.reslPrice,
                    sellprice:      row.sellPrice,
                    reslprice:      row.reslPrice,
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           row.itemActive ? (row.googleActive ? 'update' : 'insert') : 'delete'    // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getUpdateAllStock()">

    def getUpdateAllStock(page) {

        def items = [:]
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.sell_price > 5 AND ig.send=1
            ORDER BY it.accwareCatID""", page * 100, 100)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   row.sellPrice,
                    oldreslprice:   row.reslPrice,
                    sellprice:      row.sellPrice,
                    reslprice:      row.reslPrice,
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           row.itemActive ? (row.googleActive ? 'update' : 'insert') : 'delete'    // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getCompuvestStock()">
/*
SELECT it.item_id itemID,it.accwareCatID accwareID,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,CONCAT(IFNULL(gc.name,'Generic'),' > ',IFNULL(ct.name,'Generic')) catName,
it.stock_quantity qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action
FROM sklad.items it
LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
LEFT JOIN sklad.itemsAdvert ad USING(item_id)
LEFT JOIN sklad.category ct USING(category_id)
LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
LEFT JOIN sklad.itemVendor iv USING(vendor_id)
LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
WHERE it.vendor_id=2 AND it.sell_price > 5 AND it.updateTime BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
ORDER BY it.accwareCatID
LIMIT 0,1\G
*/

    //def getCompuvestStock(page, subcatid, vends) {
//            WHERE it.vendor_id=2 AND it.sell_price > 5 AND ad.froogle=TRUE AND (it.updateTime BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()) AND it.category_id=${subcatid} AND it.vendor_id IN (${vends})
//            WHERE it.vendor_id=2 AND it.sell_price > 5 AND ad.froogle=TRUE AND (it.updateTime BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW())
    def getCompuvestStock(page, pageSize) {

        def items = [:]
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,
            it.sell_price sellPrice,it.hot_price reslPrice,it.mfr_name mfrName,ad.mfrNumber mfrNumber,
            it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,
            IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.vendor_id=2 AND it.sell_price > 5 AND ig.send=1 AND (ig.updated BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW())
            ORDER BY it.accwareCatID """, page * pageSize, pageSize)
            //"ORDER BY it.accwareCatID ", page * 2, 2)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   row.sellPrice,
                    oldreslprice:   row.reslPrice,
                    sellprice:      row.sellPrice,
                    reslprice:      row.reslPrice,
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           row.itemActive ? (row.googleActive ? 'update' : 'insert') : 'delete'    // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getHolidayBackupItems()">

    def getHolidayBackupItems(page, pageSize) {

        def items = [:]
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,it.backup_price backupPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.vendor_id=2 AND it.backup_price != 0
            ORDER BY it.accwareCatID """, page * pageSize, pageSize)
            //"ORDER BY it.accwareCatID ", page * 2, 2)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   row.sellPrice,
                    oldreslprice:   row.reslPrice,
                    sellprice:      row.backupPrice, //row.sellPrice,
                    reslprice:      row.reslPrice,
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           'update'   // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getHolidayItems()">

    //def getHolidayItems(page, percent, subcatid, vends) {
    //        WHERE it.vendor_id=2 AND it.sell_price > 5 AND ad.froogle=TRUE AND it.category_id=${subcatid} AND it.vendor_id IN (${vends})
    def getHolidayItems(page, percent, pageSize) {

        def items = [:]
        def sellPrice

        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.vendor_id=2 AND it.sell_price > 5 AND ig.send=1
            ORDER BY it.accwareCatID """, page * pageSize, pageSize)
            //"ORDER BY it.accwareCatID ", page * 2, 2)
        { row ->

            sellPrice = row.sellPrice

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   sellPrice,
                    oldreslprice:   row.reslPrice,
                    sellprice:      (sellPrice - (sellPrice * percent / 100d)).round(2),
                    reslprice:      row.reslPrice,
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           row.itemActive ? (row.googleActive ? 'update' : 'insert') : 'delete'    // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getItem()">
/*
SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(IFNULL(cad.google,'')='','Generic',cad.google) catName,
IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
FROM sklad.items it
LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
LEFT JOIN sklad.itemsAdvert ad USING(item_id)
LEFT JOIN sklad.category ct USING(category_id)
LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
LEFT JOIN sklad.itemVendor iv USING(vendor_id)
LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
WHERE it.accwareCatID='827098080-08' AND it.sell_price > 5=1\G
*/

    def getItem(action, sku) {

        def items = [:]
        try {
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(IFNULL(cad.google,'')='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.active='Y' AND it.accwareCatID=? AND it.sell_price > 5""", [sku])
            { row ->

                items[row.accwareCode] = new Item(
                    field: [
                        itemid:         row.itemID,
                        accwareid:      row.accwareCode,
                        name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                        oldsellprice:   row.sellPrice,
                        oldreslprice:   row.reslPrice,
                        sellprice:      row.sellPrice,
                        reslprice:      row.reslPrice,
                        mfrname:        row.mfrName,
                        mfrnumber:      row.mfrnumber,
                        weight:         row.weight,
                        condition:      row.cond,
                        catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                        qty:            row.qty,
                        vendorname:     row.vendorName,
                        drone:          row.drone,
                        older:          row.older,
                        googlegroup:    row.googlegroup,
                        upc:            row.upc
                    ],
                    action: [
                        type:           action  // insert, update, delete
                    ],
                    status: [
                        code:           '0',
                        reason:         'Not find',
                        accware:        ''      // '', ok, error
                    ]
                )
            }
            sql.close()
        }
        catch(e) {
            println "getItem: action=${action} sku=${sku}"
        }

        return items
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAllStock()">
/*
SELECT it.item_id itemID,it.accwareCatID accwareID,it.name name,it.sell_price sellPrice,it.hot_price reslPrice,
it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,CONCAT(IFNULL(gc.name,'Generic'),' > ',IFNULL(ct.name,'Generic')) catName,
it.stock_quantity qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action
FROM sklad.items it
LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
LEFT JOIN sklad.itemsAdvert ad USING(item_id)
LEFT JOIN sklad.category ct USING(category_id)
LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
LEFT JOIN sklad.itemVendor iv USING(vendor_id)
LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
WHERE it.vendor_id!=2 AND it.sell_price > 5 AND it.updateTime BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
ORDER BY it.accwareCatID
LIMIT 0,1\G
*/

//            WHERE it.vendor_id!=2 AND it.sell_price > 5 AND ad.froogle=1 AND (ig.updated BETWEEN DATE_SUB(NOW(), INTERVAL 25 HOUR) AND NOW())
    def getAllStock(page, pageSize) {

        def items = [:]
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.vendor_id!=2 AND it.sell_price > 5 AND ig.send=1 AND (ig.updated BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW())
            ORDER BY it.accwareCatID """, page * pageSize, pageSize)
            //"ORDER BY it.accwareCatID ", page * 2, 2)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   row.sellPrice,
                    oldreslprice:   row.reslPrice,
                    sellprice:      row.sellPrice,
                    reslprice:      row.reslPrice,
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           row.itemActive ? (row.googleActive ? 'update' : 'insert') : 'delete'    // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAllStock()">

    def getNotOurStock(page, pageSize) {

        def items = [:]
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.vendor_id!=2 AND it.sell_price > 5 AND ig.send=1
            ORDER BY it.accwareCatID """, page * pageSize, pageSize)
            //"ORDER BY it.accwareCatID ", page * 2, 2)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   row.sellPrice,
                    oldreslprice:   row.reslPrice,
                    sellprice:      row.sellPrice,
                    reslprice:      row.reslPrice,
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           row.itemActive ? (row.googleActive ? 'update' : 'insert') : 'delete'    // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getRemoveStock()">

    def getRemoveStock(page, pageSize) {

        def items = [:]
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            WHERE it.active='D' AND (ig.updated BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW())
            ORDER BY it.accwareCatID """, page * pageSize, pageSize)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                ],
                action: [
                    type:           'delete'
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getRemoveAllStock()">

    def getRemoveAllStock(page, pageSize) {

        def items = [:]
        sql.eachRow(
            """SELECT item_id itemID,accwareCatID accwareCode
            FROM sklad.items
            ORDER BY accwareCatID """, page * pageSize, pageSize)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                ],
                action: [
                    type:           'delete'
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getStatus()">

    def getStatus() {

        def json = new StringBuilder()
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            WHERE it.active='D' AND (ig.updated BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW())
            ORDER BY it.accwareCatID """)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                ],
                action: [
                    type:           'delete'
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return json.toString()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getExparedStock()">
/*
SELECT it.item_id itemID,it.accwareCatID accwareID,it.name name,it.sell_price sellPrice,it.hot_price reslPrice,
it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,CONCAT(IFNULL(gc.name,'Generic'),' > ',IFNULL(ct.name,'Generic')) catName,
it.stock_quantity qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action
FROM sklad.items it
LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
LEFT JOIN sklad.itemsAdvert ad USING(item_id)
LEFT JOIN sklad.category ct USING(category_id)
LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
LEFT JOIN sklad.itemVendor iv USING(vendor_id)
LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
WHERE it.sell_price > 5 AND ig.expdate BETWEEN DATE_SUB(NOW(), INTERVAL 2 DAY) AND NOW()
ORDER BY it.accwareCatID
LIMIT 0,1\G

SELECT it.sell_price sellPrice,
it.stock_quantity qty,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,
IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action
FROM sklad.items it
LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
LEFT JOIN sklad.itemsAdvert ad USING(item_id)
LEFT JOIN sklad.category ct USING(category_id)
LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
LEFT JOIN sklad.itemVendor iv USING(vendor_id)
LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
WHERE it.sell_price > 5 AND ig.expdate BETWEEN DATE_SUB(NOW(), INTERVAL 2 DAY) AND NOW()
ORDER BY it.accwareCatID;
*/

    //def getExparedStock(page, pagesize, subcatid, vends) {
    //        WHERE it.sell_price > 5 AND ad.froogle=TRUE AND (ig.expdate BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 2 DAY)) AND it.category_id=${subcatid} AND it.vendor_id IN (${vends})
    def getExparedStock(page, pagesize) {

        def items = [:]
        sql.eachRow(
            """SELECT it.item_id itemID,it.accwareCatID accwareCode,IF(ig.itemname='',it.name,ig.itemname) name,it.sell_price sellPrice,it.hot_price reslPrice,
            it.mfr_name mfrName,ad.mfrNumber mfrNumber,it.weight weight,cs.froogle cond,IF(cad.google='','Generic',cad.google) catName,
            IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendorName,IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) older,ad.upc upc,
            IF(it.active='Y',1,0) itemActive,ig.active googleActive,ig.actionToGoogle action,ig.groups googlegroup
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
            LEFT JOIN sklad.itemsAdvert ad USING(item_id)
            LEFT JOIN sklad.category ct USING(category_id)
            LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id
            LEFT JOIN sklad.conditions cs ON cs.id=LEFT(RIGHT(it.accwareCatID,2),1)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN sklad.category gc ON ct.parent=gc.category_id
            WHERE it.sell_price > 5 AND ig.send=1 AND (ig.expdate BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 2 DAY))
            ORDER BY it.accwareCatID """, page * pagesize, pagesize)
            //"ORDER BY it.accwareCatID ", page * 2, 2)
        { row ->

            items[row.accwareCode] = new Item(
                field: [
                    itemid:         row.itemID,
                    accwareid:      row.accwareCode,
                    name:           row.name.length() > 70 ? row.name[0..69] : row.name,
                    oldsellprice:   row.sellPrice,
                    oldreslprice:   row.reslPrice,
                    sellprice:      row.sellPrice,
                    reslprice:      row.reslPrice,
                    mfrname:        row.mfrName,
                    mfrnumber:      row.mfrnumber,
                    weight:         row.weight,
                    condition:      row.cond,
                    catname:        row.catName.replaceAll(/\&/, '&amp;').replaceAll(/\>/, '&gt;'),
                    qty:            row.qty,
                    vendorname:     row.vendorName,
                    drone:          row.drone,
                    older:          row.older,
                    googlegroup:    row.googlegroup,
                    upc:            row.upc
                ],
                action: [
                    type:           row.itemActive ? (row.googleActive ? 'update' : 'insert') : 'delete'    // insert, update, delete
                ],
                status: [
                    code:           '0',
                    reason:         'Not find',
                    accware:        ''      // '', ok, error
                ]
            )
        }
        sql.close()

        return items
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="savePrice()">
    def static final SAVE_PRICE =
        """UPDATE LOW_PRIORITY sklad.items it,sklad.itemsOnGoogle ig
        SET ig.active=?,it.sell_price=?,it.hot_price=?,ig.action=?,ig.code=?,ig.reason=?,ig.accware=?,ig.expdate=?
        WHERE it.accwareCatID=? AND it.item_id=ig.item_id"""

    def savePrice(items) {

        // save price and google status
        def expdate = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new Date().plus(30))
        sql.withBatch(items.size(), SAVE_PRICE) { row ->
            items.each { accwareid, item ->
                row.addBatch(
                    item.action['type'] == 'delete' ? '0' : '1',
                    item.field['sellprice'],
                    item.field['reslprice'],
                    item.action['type'],
                    item.status['code'],
                    item.status['reason'],
                    item.status['accware'],
                    item.action['type'] == 'delete' ? '0000-00-00 00:00:00' : expdate,
                    accwareid
                )
            }
        }
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveBackupPrice()">
    def static final BACKUP_PRICE =
        "UPDATE LOW_PRIORITY sklad.items it,sklad.itemsOnGoogle ig " +
        "SET ig.active=?,it.sell_price=?,it.hot_price=?,it.backup_price=0.0,ig.action=?,ig.code=?,ig.reason=?,ig.accware=? " +
        "WHERE it.accwareCatID=? AND it.item_id=ig.item_id"

    def saveBackupPrice(items) {

        // save price and google status
        sql.withBatch(items.size(), BACKUP_PRICE) { row ->
            items.each { accwareid, item ->
                row.addBatch(
                    item.action['type'] == 'delete' ? '0' : '1',
                    item.field['sellprice'],
                    item.field['reslprice'],
                    item.action['type'],
                    item.status['code'],
                    item.status['reason'],
                    item.status['accware'],
                    accwareid
                )
            }
        }
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="saveHolidayPrice()">
    def static final HOLIDAY_PRICE =
        "UPDATE LOW_PRIORITY sklad.items it,sklad.itemsOnGoogle ig " +
        "SET ig.active=?,it.sell_price=?,it.hot_price=?,it.backup_price=?,ig.action=?,ig.code=?,ig.reason=?,ig.accware=? " +
        "WHERE it.accwareCatID=? AND it.item_id=ig.item_id"

    def saveHolidayPrice(items) {

        // save price and google status
        sql.withBatch(items.size(), HOLIDAY_PRICE) { row ->
            items.each { accwareid, item ->
                row.addBatch(
                    item.action['type'] == 'delete' ? '0' : '1',
                    item.field['sellprice'],
                    item.field['reslprice'],
                    item.field['oldsellprice'],
                    item.action['type'],
                    item.status['code'],
                    item.status['reason'],
                    item.status['accware'],
                    accwareid
                )
            }
        }
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="savePrice()">
    def static final SAVE_RESULT =
        """UPDATE LOW_PRIORITY sklad.itemsOnGoogle ig
        SET ig.active=?,ig.action=?,ig.code=?,ig.reason=?,ig.accware=?,ig.expdate=?
        WHERE ig.accwareCatID=?"""

    def saveResult(items) {

        // save google status
        def expdate = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new Date().plus(30))
        sql.withBatch(items.size(), SAVE_RESULT) { row ->
            items.each { accwareid, item ->

                switch(item.action['type']) {
                    case ['insert', 'update', 'delete']:
                        row.addBatch(
                            item.action['type'] == 'delete' ? '0' : '1',
                            item.action['type'],
                            item.status['code'],
                            item.status['reason'],
                            item.status['accware'],
                            item.action['type'] == 'delete' ? '0000-00-00 00:00:00' : expdate,
                            accwareid)
                        break
                    default: // query not write to db
                        //println ">>>save result action=${item.action['type']}"
                        break
                } //sw
            } // each
        }
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updatePrice()">
    def static final UPDATE_PRICE =
        "UPDATE LOW_PRIORITY sklad.items it,sklad.itemsOnGoogle ig " +
        "SET it.active='Y',ig.actionToGoogle='update'," +
        "it.sell_price=:sellprice,it.hot_price=:reslprice " +
        "WHERE it.accwareCatID=:accwareid AND it.item_id=ig.item_id"

    def updatePrice(userid, accwareid, sellprice, reslprice) {

        if(userid != '0') { // userid == 0 not store to history
            // google user update history
            sql.call("{ call sklad.googleUserHistory(?,?,?,?) }", [ // in_userID,in_accwareCode,in_newSellPrice,in_newResellerPrice
                userid,
                accwareid,
                sellprice,
                reslprice
            ])
            sql.close()
        }

        // save price and google status
        sql.executeUpdate(UPDATE_PRICE, [sellprice: sellprice, reslprice: reslprice, accwareid: accwareid])
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="initTempTable()">

    def final static DROP_TABLES = "DROP TABLE IF EXISTS advert.itemsSortForFroogle,advert.itemsGroupForFroogle"

    def final static MAKE_TABLE_SORT =
	"CREATE TABLE advert.itemsSortForFroogle (" +
	"item_id     int(10) unsigned NOT NULL default 0," +
	"mfrNumber   varchar(30) NOT NULL default ''," +
	"conditionID tinyint(1) unsigned NOT NULL default 0," +
	"KEY idx_item_id(item_id)," +
	"KEY idx_mfrNumber(mfrNumber)," +
	"KEY idx_conditionID(conditionID)" +
	") ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='sort by sell_price for froogle'"

    def final static MAKE_TABLE_GROUP =
	"CREATE TABLE advert.itemsGroupForFroogle (" +
	"item_id     int(10) unsigned NOT NULL default 0," +
	"mfrNumber   varchar(30) NOT NULL default ''," +
	"conditionID tinyint(1) unsigned NOT NULL default 0," +
	"KEY idx_item_id(item_id)" +
	") ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='group by mfrNumber,conditions for froogle'"

    def final static FILL_ITEMS_SORT_TABLE =
	"INSERT INTO advert.itemsSortForFroogle " +
        "(SELECT it.item_id item_id,ad.mfrNumber mfrNumber,LEFT(RIGHT(it.accwareCatID,2),1) conditionID " +
        "FROM sklad.items it " +
	"LEFT JOIN sklad.itemsAdvert ad USING(item_id) " +
        "WHERE it.active='Y' AND ad.mfrNumber<>'' AND ad.froogle=1 " +
        "ORDER BY it.sell_price)"

    def final static FILL_ITEMS_GROUP_TABLE =
	"INSERT INTO advert.itemsGroupForFroogle " +
        "(SELECT isa.item_id item_id,isa.mfrNumber mfrNumber,isa.conditionID conditionID " +
        "FROM advert.itemsSortForFroogle isa " +
        "GROUP BY isa.mfrNumber,isa.conditionID)"

    // this for fix mysql need sort by sell_price and group by mfrNumber and conditions
    def void initTempTable() {

        sql.execute(DROP_TABLES)
        sql.close()
        sql.execute(MAKE_TABLE_SORT)
        sql.close()
        sql.execute(MAKE_TABLE_GROUP)
        sql.close()

        sql.execute(FILL_ITEMS_SORT_TABLE)
        sql.close()
        sql.execute(FILL_ITEMS_GROUP_TABLE)
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVendorsFromPefokarta()">
//
// our items first in file (ourstock==true - our stock)
// CVS=2, ADV=27, AAA=29, CAS=83
// advert_id = 18 - froogle
/*
SELECT subcat_id,GROUP_CONCAT(vendor_id) vendors
FROM advert.advertSend
WHERE advert_id=18 AND vendor_id IN(9) GROUP BY subcat_id;
*/
    def getVendorsFromPefokart(vendors) {

        def table = [:]
        sql.eachRow(
            "SELECT subcat_id,CAST(GROUP_CONCAT(vendor_id) AS CHAR) vendors " +
            "FROM advert.advertSend " +
            "WHERE advert_id=18 AND vendor_id ${vendors} GROUP BY subcat_id")
        { row ->
            table[row.subcat_id] = row.vendors
        }
        sql.close()

        return table
    }
/*
    def getVendorsTablePefokart() {

        def table = [:]
        sql.eachRow(
            """SELECT subcat_id,CAST(GROUP_CONCAT(vendor_id) AS CHAR) vendors
            FROM advert.advertSend
            WHERE advert_id=18
            GROUP BY subcat_id""")
        { row ->
            table[row.subcat_id] = row.vendors
        }
        sql.close()

        return table
    }
*/
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="makeFile()">

    def final static HEAD_LINE = "id\ttitle\tdescription\tgoogle_product_category\tproduct_type\tlink\timage_link\tcondition\tavailability\tprice\tbrand\tgtin\tmpn\tshipping_weight"

    def makeFile(vendors, lowPrice, fileName, advertID) {

        def countItems = 0
        def img = "http://img.compuvest.com/0/%1s.jpg"
        def link = "http://www.compuvest.com/Search.jsp?Search=%1s&advsite=froogle&sku=%1s&dp=%1s:%1s:%1s:%1s:%1s"

        switch(advertID) {
            case COMPUVESTCOM:
                break
            case COMPUVESTUS:
                img = "http://www.compuvest.us/xml/images/%1s.jpeg"
                link = "http://www.compuvest.us/GetProduct.aspx?ITM=%1s&REF=Froogle&sku=%1s&dp=%1s:%1s:%1s:%1s:%1s"
                break
            case AMEX:
                link = "http://www.compuvest.com/Search.jsp?Search=%1s&advsite=amex&sku=%1s&dp=%1s:%1s:%1s:%1s:%1s"
                break
        }

        new File("/var/lighttpd/1.4/docroot/advert/froogle/${fileName}").withWriter { out ->
            out.writeLine(HEAD_LINE)

            vendors.each { catid, vendids ->
                //println "catid=${catid} vendids=${vendids}"

                sql.eachRow(
                "SELECT it.accwareCatID id,IF(ig.itemname='',it.name,ig.itemname) title,IF(cad.google='','Generic',cad.google) product_category," +
                "ta.mfrNumber mpn,IF(it.stock_quantity=0,3,it.stock_quantity) qty,iv.alias vendName,CAST(it.hot_price * 100 AS UNSIGNED) reslPrice," +
                "IF(it.drone=0,0,1) drone,IFNULL(DATEDIFF(NOW(),it.stockDate),0) old,cs.froogle cond,it.sell_price price,ad.mfrName mpname," +
                "ad.upc gtin,it.weight weight " +
                "FROM advert.itemsGroupForFroogle ta " +
                "LEFT JOIN sklad.items it USING(item_id) " +
                "LEFT JOIN sklad.itemsAdvert ad USING(item_id) " +
                "LEFT JOIN sklad.itemVendor iv USING(vendor_id) " +
                "LEFT JOIN sklad.category sc USING(category_id) " +
                "LEFT JOIN sklad.category gc ON sc.parent=gc.category_id " +
                "LEFT JOIN sklad.categoryAdvert cad ON it.category_id=cad.category_id " +
                "LEFT JOIN sklad.conditions cs ON cs.id=ta.conditionID " +
                "WHERE it.category_id=${catid} AND it.sell_price > ${lowPrice} AND it.vendor_id IN (${vendids})")
                { row ->
                    countItems++

                    out.writeLine(
                        "${row.id}\t" +                     // id
                        "${row.title}\t" +                  // title
                        "${row.title}\t" +                  // description
                        "${row.product_category}\t" +       // google_product_category
                        "${row.product_category}\t" +       // product_type
                        "${String.format(link, row.mpn,row.id,row.qty,row.vendName,row.reslPrice,row.drone,row.old)}\t" + // site url
                        "${String.format(img, row.id)}\t" + // image url
                        "${row.cond}\t" +                   // condition
                        "in stock\t" +
                        "${row.price}\t" +
                        "${row.mpname}\t" +                 // brand
                        "${row.gtin}\t" +                   // upc
                        "${row.mpn}\t" +                    // manufacure part number
                        "${row.weight}"                     // shippping weight
                    )
                }
                sql.close()
            } // vendors
        } // new File

        return countItems
    }
// </editor-fold>


}

/*
can be shortened to
closure?.call(con)


def sql = new Sql(datasource)

Now you can invoke sql, e.g. to create a table:

 sql.execute '''
     create table PROJECT (
         id integer not null,
         name varchar(50),
         url varchar(100),
     )
 '''


Or insert a row using JDBC PreparedStatement inspired syntax:

 def params = [10, 'Groovy', 'http://groovy.codehaus.org']
 sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', params


Or insert a row using GString syntax:

 def map = [id:20, name:'Grails', url:'http://grails.codehaus.org']
 sql.execute "insert into PROJECT (id, name, url) values ($map.id, $map.name, $map.url)"


Or a row update:

 def newUrl = 'http://grails.org'
 def project = 'Grails'
 sql.executeUpdate "update PROJECT set url=$newUrl where name=$project"


Now try a query using eachRow:

 println 'Some GR8 projects:'
 sql.eachRow('select * from PROJECT') { row ->
     println "${row.name.padRight(10)} ($row.url)"
 }



Now try a query using rows:

 def rows = sql.rows("select * from PROJECT where name like 'Gra%'")
 assert rows.size() == 2
 println rows.join('\n')


with output like this:

 [ID:20, NAME:Grails, URL:http://grails.org]
 [ID:40, NAME:Gradle, URL:http://gradle.org]


Also, eachRow and rows support paging. Here's an example:

 sql.eachRow('select * from PROJECT', 2, 2) { row ->
     println "${row.name.padRight(10)} ($row.url)"
 }
 **/